using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.ObjectBuilder;
using GC2.ExceptionHandler;
using GC2.ExceptionHandler.Services;
using GC2.Entities.Logistica;
using GC2.DataAccess;

namespace GC2.DataAccess.DALogistica
{
    public class DAHRutaDet
    {
        private Database _database;        // Objeto DataBase del Data Access Application Block

        #region Propiedades miembro
        /// <summary>
        /// Seteo la propiedad _database de la clase.
        /// </summary>
        private Database DataBase
        {
            get { return _database; }
            set { _database = value; }
        }
        #endregion

        #region Constructor
        /// <summary>
        /// Contructor de la clase sin parametro. Crea un objeto DataBase
        /// del Data AppBlock tomando el defaultDatabase del app.config.
        /// </summary>
        //public AplicacionesDataAccess()
        //{
        //    this.DataBase = DatabaseFactory.CreateDatabase();
        //}
        #endregion

        #region DAHRutaDetInsert
        /// <summary>
        /// Inserta un detalle en la tabla HRutaDet
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRutaDet"></param>
        public void DAHRutaDetInsert(string Connection, HRutaDet hrutadet)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRutaDet_Insert");

            try
            {
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdHRuta", DbType.Int64, hrutadet.IdHRuta);
                    DataBase.AddInParameter(cmd, "@Pagina", DbType.Int16, hrutadet.Pagina);
                    DataBase.AddInParameter(cmd, "@Item", DbType.Int16, hrutadet.Item);
                    DataBase.AddInParameter(cmd, "@Origen", DbType.String, hrutadet.Origen);
                    DataBase.AddInParameter(cmd, "@IdCliente", DbType.Int64, hrutadet.IdCliente);
                    DataBase.AddInParameter(cmd, "@Cliente", DbType.String, hrutadet.Cliente);
                    DataBase.AddInParameter(cmd, "@Remito", DbType.Int32, hrutadet.Remito);
                    DataBase.AddInParameter(cmd, "@Embalaje", DbType.String, hrutadet.Embalaje);
                    DataBase.AddInParameter(cmd, "@Cobro", DbType.String, hrutadet.Cobro);
                    DataBase.AddInParameter(cmd, "@TipoEntrega", DbType.String, hrutadet.TipoEntrega);
                    DataBase.AddInParameter(cmd, "@Destino", DbType.String, hrutadet.Destino);
                    DataBase.AddInParameter(cmd, "@Destinatario", DbType.String, hrutadet.Destinatario);
                    DataBase.AddInParameter(cmd, "@Kilos", DbType.Decimal, hrutadet.Kilos);
                    DataBase.AddInParameter(cmd, "@Bultos", DbType.Decimal, hrutadet.Bultos);
                    DataBase.AddInParameter(cmd, "@PorCobrar", DbType.Decimal, hrutadet.PorCobrar);
                    DataBase.AddInParameter(cmd, "@Entregar", DbType.Int16, hrutadet.Entregar);
                    DataBase.AddInParameter(cmd, "@FechaE", DbType.DateTime, hrutadet.FechaE);
                    DataBase.AddInParameter(cmd, "@usuarioE", DbType.Int16, hrutadet.usuarioE);
                    DataBase.AddInParameter(cmd, "@Imputar", DbType.Int16, hrutadet.Imputar);
                    DataBase.AddInParameter(cmd, "@FechaI", DbType.DateTime, hrutadet.FechaI);
                    DataBase.AddInParameter(cmd, "@IdRemito", DbType.Int64, hrutadet.IdRemito);
                    DataBase.AddInParameter(cmd, "@usuarioI", DbType.Int16, hrutadet.usuarioI);
                    DataBase.AddInParameter(cmd, "@Conformar", DbType.Int16, hrutadet.Conformar);
                    DataBase.AddInParameter(cmd, "@FechaC", DbType.DateTime, hrutadet.FechaC);
                    DataBase.AddInParameter(cmd, "@usuarioC", DbType.Int16, hrutadet.usuarioC);
                    DataBase.AddInParameter(cmd, "@ValDeclarado", DbType.Decimal, hrutadet.ValDeclarado);
                    DataBase.AddInParameter(cmd, "@Valorizar", DbType.Int16, hrutadet.Valorizar);
                    DataBase.AddInParameter(cmd, "@FechaV", DbType.DateTime, hrutadet.FechaV);
                    DataBase.AddInParameter(cmd, "@usuarioV", DbType.Int16, hrutadet.usuarioV);
                    DataBase.AddInParameter(cmd, "@CTroncal", DbType.Decimal, hrutadet.CTroncal);
                    DataBase.AddInParameter(cmd, "@CEnRe", DbType.Decimal, hrutadet.CEnRe);
                    DataBase.AddInParameter(cmd, "@CRedes", DbType.Decimal, hrutadet.CRedes);
                    DataBase.AddInParameter(cmd, "@COtros", DbType.Decimal, hrutadet.COtros);
                    DataBase.AddInParameter(cmd, "@TCostos", DbType.Decimal, hrutadet.TCostos);
                    DataBase.AddInParameter(cmd, "@FFlete", DbType.Decimal, hrutadet.FFlete);
                    DataBase.AddInParameter(cmd, "@FSegPor", DbType.Decimal, hrutadet.FSegPor);
                    DataBase.AddInParameter(cmd, "@FSeguro", DbType.Decimal, hrutadet.FSeguro);
                    DataBase.AddInParameter(cmd, "@FEnRe", DbType.Decimal, hrutadet.FEnRe);
                    DataBase.AddInParameter(cmd, "@FCustodia", DbType.Decimal, hrutadet.FCustodia);
                    DataBase.AddInParameter(cmd, "@FOtros", DbType.Decimal, hrutadet.FOtros);
                    DataBase.AddInParameter(cmd, "@TFactura", DbType.Decimal, hrutadet.TFactura);
                    DataBase.AddInParameter(cmd, "@IdRubro", DbType.Int16, hrutadet.IdRubro);
                    DataBase.AddInParameter(cmd, "@IdSubRubro", DbType.Int16, hrutadet.IdSubRubro);
                    DataBase.AddInParameter(cmd, "@IdArticulo", DbType.Int16, hrutadet.IdArticulo);
                    DataBase.AddInParameter(cmd, "@Articulo", DbType.String, hrutadet.Articulo);
                    DataBase.AddInParameter(cmd, "@Cantidad", DbType.Decimal, hrutadet.Cantidad);
                    DataBase.AddInParameter(cmd, "@M3", DbType.Int16, hrutadet.M3);
                    DataBase.AddInParameter(cmd, "@Lote", DbType.Int64, hrutadet.Lote);

                    DataBase.ExecuteNonQuery(cmd);
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaDetUpdateEntregar
        /// <summary>
        /// Actualiza detalle marcando item como Entregar
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRutaDet"></param>
        public void DAHRutaDetUpdateEntregar(string Connection, HRutaDet hrutadet)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRutaDet_UpdateEntregar");

            try
            {
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdHRuta", DbType.Int64, hrutadet.IdHRuta);
                    DataBase.AddInParameter(cmd, "@Pagina", DbType.Int16, hrutadet.Pagina);
                    DataBase.AddInParameter(cmd, "@Item", DbType.Int16, hrutadet.Item);
                    DataBase.AddInParameter(cmd, "@Entregar", DbType.Int16, hrutadet.Entregar);
                    DataBase.AddInParameter(cmd, "@FechaE", DbType.DateTime, hrutadet.FechaE);
                    DataBase.AddInParameter(cmd, "@usuarioE", DbType.Int16, hrutadet.usuarioE);

                    DataBase.ExecuteNonQuery(cmd);
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaDetUpdateImputar
        /// <summary>
        /// Actualiza detalle marcando item como Imputar
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRutaDet"></param>
        public void DAHRutaDetUpdateImputar(string Connection, HRutaDet hrutadet)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRutaDet_UpdateImputar");

            try
            {
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdHRuta", DbType.Int64, hrutadet.IdHRuta);
                    DataBase.AddInParameter(cmd, "@Pagina", DbType.Int16, hrutadet.Pagina);
                    DataBase.AddInParameter(cmd, "@Item", DbType.Int16, hrutadet.Item);
                    DataBase.AddInParameter(cmd, "@Imputar", DbType.Int16, hrutadet.Imputar);
                    DataBase.AddInParameter(cmd, "@FechaI", DbType.DateTime, hrutadet.FechaI);
                    DataBase.AddInParameter(cmd, "@IdRemito", DbType.Int64, hrutadet.IdRemito);
                    DataBase.AddInParameter(cmd, "@usuarioI", DbType.Int16, hrutadet.usuarioI);

                    DataBase.ExecuteNonQuery(cmd);
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaDetUpdateConformar
        /// <summary>
        /// Actualiza detalle marcando item como Conformado
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRutaDet"></param>
        public void DAHRutaDetUpdateConformar(string Connection, HRutaDet hrutadet)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRutaDet_UpdateConformar");

            try
            {
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdHRuta", DbType.Int64, hrutadet.IdHRuta);
                    DataBase.AddInParameter(cmd, "@Pagina", DbType.Int16, hrutadet.Pagina);
                    DataBase.AddInParameter(cmd, "@Item", DbType.Int16, hrutadet.Item);
                    DataBase.AddInParameter(cmd, "@Conformar", DbType.Int16, hrutadet.Conformar);
                    DataBase.AddInParameter(cmd, "@FechaC", DbType.DateTime, hrutadet.FechaC);
                    DataBase.AddInParameter(cmd, "@usuarioC", DbType.Int16, hrutadet.usuarioC);

                    DataBase.ExecuteNonQuery(cmd);
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaDetUpdateValorizar
        /// <summary>
        /// Actualiza detalle marcando item como Valorizado
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRutaDet"></param>
        public void DAHRutaDetUpdateValorizar(string Connection, HRutaDet hrutadet)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRutaDet_UpdateValorizar");

            try
            {
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdHRuta", DbType.Int64, hrutadet.IdHRuta);
                    DataBase.AddInParameter(cmd, "@Pagina", DbType.Int16, hrutadet.Pagina);
                    DataBase.AddInParameter(cmd, "@Item", DbType.Int16, hrutadet.Item);
                    DataBase.AddInParameter(cmd, "@Valorizar", DbType.Int16, hrutadet.Valorizar);
                    DataBase.AddInParameter(cmd, "@FechaV", DbType.DateTime, hrutadet.FechaV);
                    DataBase.AddInParameter(cmd, "@usuarioV", DbType.Int16, hrutadet.usuarioV);
                    DataBase.AddInParameter(cmd, "@CTroncal", DbType.Decimal, hrutadet.CTroncal);
                    DataBase.AddInParameter(cmd, "@CEnRe", DbType.Decimal, hrutadet.CEnRe);
                    DataBase.AddInParameter(cmd, "@CRedes", DbType.Decimal, hrutadet.CRedes);
                    DataBase.AddInParameter(cmd, "@COtros", DbType.Decimal, hrutadet.COtros);
                    DataBase.AddInParameter(cmd, "@TCostos", DbType.Decimal, hrutadet.TCostos);
                    DataBase.AddInParameter(cmd, "@FFlete", DbType.Decimal, hrutadet.FFlete);
                    DataBase.AddInParameter(cmd, "@FSegPor", DbType.Decimal, hrutadet.FSegPor);
                    DataBase.AddInParameter(cmd, "@FSeguro", DbType.Decimal, hrutadet.FSeguro);
                    DataBase.AddInParameter(cmd, "@FEnRe", DbType.Decimal, hrutadet.FEnRe);
                    DataBase.AddInParameter(cmd, "@FCustodia", DbType.Decimal, hrutadet.FCustodia);
                    DataBase.AddInParameter(cmd, "@FOtros", DbType.Decimal, hrutadet.FOtros);
                    DataBase.AddInParameter(cmd, "@TFactura", DbType.Decimal, hrutadet.TFactura);

                    DataBase.ExecuteNonQuery(cmd);
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaDetGetAll
        /// <summary>
        /// Devuelve lista de HRutaDet de una Hoja de Ruta
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRutaDet"></param>
        /// <returns><List>HRutaDet</List></returns>
        public List<HRutaDet> DAHRutaDetGetAll(string Connection, HRutaDet hrutadet)
        {
            try
            {
                this.DataBase = DatabaseFactory.CreateDatabase(Connection);
                DbCommand cmd = DataBase.GetStoredProcCommand("HRutaDet_GetAll");

                List<HRutaDet> listaHRutaDet = new List<HRutaDet>();
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdHRuta", DbType.Int64, hrutadet.IdHRuta);

                    IDataReader Odbreader = DataBase.ExecuteReader(cmd);
                    while (Odbreader.Read())
                    {
                        HRutaDet oHRutaDet = new HRutaDet();

                        oHRutaDet.IdHRuta  = Convert.ToInt64(Odbreader["IdHRuta"]);
                        oHRutaDet.Pagina = (int)(Odbreader["Pagina"]);
                        oHRutaDet.Item = (int)(Odbreader["Item"]);
                        oHRutaDet.Origen = (string)Odbreader["Origen"];
                        oHRutaDet.IdCliente = Convert.ToInt64(Odbreader["IdCliente"]);
                        oHRutaDet.Cliente = (string)Odbreader["Cliente"];
                        oHRutaDet.Remito = Convert.ToInt32(Odbreader["Remito"]);
                        oHRutaDet.Embalaje = (string)Odbreader["Embalaje"];
                        oHRutaDet.Cobro = (string)Odbreader["Cobro"];
                        oHRutaDet.TipoEntrega = (string)Odbreader["TipoEntrega"];
                        oHRutaDet.Destino = (string)Odbreader["Destino"];
                        oHRutaDet.Destinatario = (string)Odbreader["Destinatario"];
                        oHRutaDet.Kilos = (decimal)Odbreader["Kilos"];
                        oHRutaDet.Bultos = (int)(Odbreader["Bultos"]);
                        oHRutaDet.PorCobrar = (decimal)Odbreader["PorCobrar"];
                        oHRutaDet.Entregar = (int)(Odbreader["Entregar"]);
                        oHRutaDet.FechaE = (DateTime)Odbreader["FechaE"];
                        oHRutaDet.usuarioE = (int)(Odbreader["usuarioE"]);
                        oHRutaDet.Imputar = (int)(Odbreader["Imputar"]);
                        oHRutaDet.FechaI = (DateTime)Odbreader["FechaI"];
                        oHRutaDet.IdRemito = Convert.ToInt64(Odbreader["IdRemito"]);
                        oHRutaDet.usuarioI = (int)(Odbreader["usuarioI"]);

                        listaHRutaDet.Add(oHRutaDet);
                    }
                    Odbreader.Close();
                    Odbreader.Dispose();
                }
                return listaHRutaDet;
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaDetGetAllByPagina
        /// <summary>
        /// Devuelve lista de HRutaDet de una Hoja de Ruta y una Pagina
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRutaDet"></param>
        /// <returns><List>HRutaDet</List></returns>
        public List<HRutaDet> DAHRutaDetGetAllByPagina(string Connection, HRutaDet hrutadet)
        {
            try
            {
                this.DataBase = DatabaseFactory.CreateDatabase(Connection);
                DbCommand cmd = DataBase.GetStoredProcCommand("HRutaDet_GetAllByPagina");

                List<HRutaDet> listaHRutaDet = new List<HRutaDet>();
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdHRuta", DbType.Int64, hrutadet.IdHRuta);
                    DataBase.AddInParameter(cmd, "@Pagina", DbType.Int64, hrutadet.Pagina);

                    IDataReader Odbreader = DataBase.ExecuteReader(cmd);
                    while (Odbreader.Read())
                    {
                        HRutaDet oHRutaDet = new HRutaDet();

                        oHRutaDet.IdHRuta = Convert.ToInt64(Odbreader["IdHRuta"]);
                        oHRutaDet.Pagina = (int)(Odbreader["Pagina"]);
                        oHRutaDet.Item = (int)(Odbreader["Item"]);
                        oHRutaDet.Origen = (string)Odbreader["Origen"];
                        oHRutaDet.IdCliente = Convert.ToInt64(Odbreader["IdCliente"]);
                        oHRutaDet.Cliente = (string)Odbreader["Cliente"];
                        oHRutaDet.Remito = Convert.ToInt32(Odbreader["Remito"]);
                        oHRutaDet.Embalaje = (string)Odbreader["Embalaje"];
                        oHRutaDet.Cobro = (string)Odbreader["Cobro"];
                        oHRutaDet.TipoEntrega = (string)Odbreader["TipoEntrega"];
                        oHRutaDet.Destino = (string)Odbreader["Destino"];
                        oHRutaDet.Destinatario = (string)Odbreader["Destinatario"];
                        oHRutaDet.Kilos = (decimal)Odbreader["Kilos"];
                        oHRutaDet.Bultos = (int)(Odbreader["Bultos"]);
                        oHRutaDet.PorCobrar = (decimal)Odbreader["PorCobrar"];
                        oHRutaDet.Entregar = (int)(Odbreader["Entregar"]);
                        oHRutaDet.FechaE = (DateTime)Odbreader["FechaE"];
                        oHRutaDet.usuarioE = (int)(Odbreader["usuarioE"]);
                        oHRutaDet.Imputar = (int)(Odbreader["Imputar"]);
                        oHRutaDet.FechaI = (DateTime)Odbreader["FechaI"];
                        oHRutaDet.IdRemito = Convert.ToInt64(Odbreader["IdRemito"]);
                        oHRutaDet.usuarioI = (int)(Odbreader["usuarioI"]);
                        oHRutaDet.Conformar = (int)(Odbreader["Conformar"]);
                        oHRutaDet.FechaC = (DateTime)Odbreader["FechaC"];
                        oHRutaDet.usuarioC = (int)(Odbreader["usuarioC"]);
                        oHRutaDet.Valorizar = (int)(Odbreader["Valorizar"]);
                        oHRutaDet.FechaV = (DateTime)Odbreader["FechaV"];
                        oHRutaDet.usuarioV = (int)(Odbreader["usuarioV"]);
                        oHRutaDet.IdRubro = (int)Odbreader["IdRubro"];
                        oHRutaDet.IdSubRubro = (int)Odbreader["IdSubRubro"];
                        oHRutaDet.IdArticulo = (int)Odbreader["IdArticulo"];
                        oHRutaDet.Lote = Convert.ToInt64(Odbreader["Lote"]);
                        oHRutaDet.Cantidad = (decimal)Odbreader["Cantidad"];                        

                        listaHRutaDet.Add(oHRutaDet);
                    }
                    Odbreader.Close();
                    Odbreader.Dispose();
                }
                return listaHRutaDet;
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaDetGetAllByCliente
        /// <summary>
        /// Devuelve lista de HRutaDet de un Cliente
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRutaDet"></param>
        /// <returns><List>HRutaDet</List></returns>
        public List<HRutaDet> DAHRutaDetGetAllByCliente(string Connection, HRutaDet hrutadet)
        {
            try
            {
                this.DataBase = DatabaseFactory.CreateDatabase(Connection);
                DbCommand cmd = DataBase.GetStoredProcCommand("HRutaDet_GetAllByCliente");

                List<HRutaDet> listaHRutaDet = new List<HRutaDet>();
                int iItemL = 0;
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdCliente", DbType.Int64, hrutadet.IdCliente);

                    IDataReader Odbreader = DataBase.ExecuteReader(cmd);
                    while (Odbreader.Read())
                    {
                        HRutaDet oHRutaDet = new HRutaDet();

                        oHRutaDet.IdHRuta = Convert.ToInt64(Odbreader["IdHRuta"]);
                        oHRutaDet.Pagina = (int)(Odbreader["Pagina"]);
                        oHRutaDet.Item = (int)(Odbreader["Item"]);
                        oHRutaDet.Origen = (string)Odbreader["Origen"];
                        oHRutaDet.IdCliente = Convert.ToInt64(Odbreader["IdCliente"]);
                        oHRutaDet.Cliente = (string)Odbreader["Cliente"];
                        oHRutaDet.Remito = Convert.ToInt32(Odbreader["Remito"]);
                        oHRutaDet.Embalaje = (string)Odbreader["Embalaje"];
                        oHRutaDet.Cobro = (string)Odbreader["Cobro"];
                        oHRutaDet.TipoEntrega = (string)Odbreader["TipoEntrega"];
                        oHRutaDet.Destino = (string)Odbreader["Destino"];
                        oHRutaDet.Destinatario = (string)Odbreader["Destinatario"];
                        oHRutaDet.Kilos = (decimal)Odbreader["Kilos"];
                        oHRutaDet.Bultos = (int)(Odbreader["Bultos"]);
                        oHRutaDet.PorCobrar = (decimal)Odbreader["PorCobrar"];
                        oHRutaDet.Entregar = (int)(Odbreader["Entregar"]);
                        oHRutaDet.FechaE = (DateTime)Odbreader["FechaE"];
                        oHRutaDet.usuarioE = (int)(Odbreader["usuarioE"]);
                        oHRutaDet.Imputar = (int)(Odbreader["Imputar"]);
                        oHRutaDet.FechaI = (DateTime)Odbreader["FechaI"];
                        oHRutaDet.IdRemito = Convert.ToInt64(Odbreader["IdRemito"]);
                        oHRutaDet.usuarioI = (int)(Odbreader["usuarioI"]);
                        oHRutaDet.Conformar = (int)(Odbreader["Conformar"]);
                        oHRutaDet.FechaC = (DateTime)Odbreader["FechaC"];
                        oHRutaDet.usuarioC = (int)(Odbreader["usuarioC"]);
                        oHRutaDet.Valorizar = (int)(Odbreader["Valorizar"]);
                        oHRutaDet.FechaV = (DateTime)Odbreader["FechaV"];
                        oHRutaDet.usuarioV = (int)(Odbreader["usuarioV"]);
                        oHRutaDet.Cantidad = (decimal)Odbreader["Cantidad"];
                        oHRutaDet.TFactura = (decimal)Odbreader["TFactura"];
                        oHRutaDet.M3 = (int)Odbreader["M3"];

                        // Datos para Informes
                        oHRutaDet.Fecha = (DateTime)Odbreader["Fecha"];
                        oHRutaDet.HojaRuta = oHRutaDet.IdHRuta.ToString();
                        oHRutaDet.HojaRuta += "/";
                        oHRutaDet.HojaRuta += oHRutaDet.Pagina.ToString();
                        if (oHRutaDet.Item < 10)
                        {
                            oHRutaDet.HojaRuta += " -  ";
                        }
                        else
                        {
                            oHRutaDet.HojaRuta += " - ";
                        }
                        oHRutaDet.HojaRuta += oHRutaDet.Item.ToString();
                        iItemL++;
                        oHRutaDet.ItemL = iItemL;

                        listaHRutaDet.Add(oHRutaDet);
                    }
                    Odbreader.Close();
                    Odbreader.Dispose();
                }
                return listaHRutaDet;
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaDetDelete
        /// <summary>
        /// Borra todos los detalles en la tabla HRutaDet de una Pagina de Hoja de Ruta
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRutaDet"></param>
        public void DAHRutaDetDelete(string Connection, HRutaDet hrutadet)
        {
            this.DataBase = DatabaseFactory.CreateDatabase(Connection);
            DbCommand cmd = DataBase.GetStoredProcCommand("HRutaDet_Delete");

            try
            {
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdHRuta", DbType.Int64, hrutadet.IdHRuta);
                    DataBase.AddInParameter(cmd, "@Pagina", DbType.Int16, hrutadet.Pagina);

                    DataBase.ExecuteNonQuery(cmd);
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DAHRutaDetGetOneByRemito
        /// <summary>
        /// Devuelve objeto HRutaDet de IdCliente por un nro. de Remito
        /// </summary>
        /// <param name="Connection"></param>
        /// <param name="HRutaDet"></param>
        /// <returns>HRutaDet</returns>
        public HRutaDet DAHRutaDetGetOneByRemito(string Connection, HRutaDet hrutadet)
        {
            try
            {
                this.DataBase = DatabaseFactory.CreateDatabase(Connection);
                DbCommand cmd = DataBase.GetStoredProcCommand("HRutaDet_GetOneByRemito");

                List<HRutaDet> listaHRutaDet = new List<HRutaDet>();
                using (cmd)
                {
                    DataBase.AddInParameter(cmd, "@IdCliente", DbType.Int64, hrutadet.IdCliente);
                    DataBase.AddInParameter(cmd, "@Remito", DbType.Int32, hrutadet.Remito);

                    IDataReader Odbreader = DataBase.ExecuteReader(cmd);
                    while (Odbreader.Read())
                    {
                        HRutaDet oHRutaDet = new HRutaDet();

                        oHRutaDet.IdHRuta = Convert.ToInt64(Odbreader["IdHRuta"]);
                        oHRutaDet.Pagina = (int)(Odbreader["Pagina"]);
                        oHRutaDet.Item = (int)(Odbreader["Item"]);
                        oHRutaDet.Origen = (string)Odbreader["Origen"];
                        oHRutaDet.IdCliente = Convert.ToInt64(Odbreader["IdCliente"]);
                        oHRutaDet.Cliente = (string)Odbreader["Cliente"];
                        oHRutaDet.Remito = Convert.ToInt32(Odbreader["Remito"]);
                        oHRutaDet.Embalaje = (string)Odbreader["Embalaje"];
                        oHRutaDet.Cobro = (string)Odbreader["Cobro"];
                        oHRutaDet.TipoEntrega = (string)Odbreader["TipoEntrega"];
                        oHRutaDet.Destino = (string)Odbreader["Destino"];
                        oHRutaDet.Destinatario = (string)Odbreader["Destinatario"];
                        oHRutaDet.Kilos = (decimal)Odbreader["Kilos"];
                        oHRutaDet.Bultos = (int)(Odbreader["Bultos"]);
                        oHRutaDet.PorCobrar = (decimal)Odbreader["PorCobrar"];
                        oHRutaDet.Entregar = (int)(Odbreader["Entregar"]);
                        oHRutaDet.FechaE = (DateTime)Odbreader["FechaE"];
                        oHRutaDet.usuarioE = (int)(Odbreader["usuarioE"]);
                        oHRutaDet.Imputar = (int)(Odbreader["Imputar"]);
                        oHRutaDet.FechaI = (DateTime)Odbreader["FechaI"];
                        oHRutaDet.IdRemito = Convert.ToInt64(Odbreader["IdRemito"]);
                        oHRutaDet.usuarioI = (int)(Odbreader["usuarioI"]);
                        oHRutaDet.Conformar = (int)(Odbreader["Conformar"]);
                        oHRutaDet.FechaC = (DateTime)Odbreader["FechaC"];
                        oHRutaDet.usuarioC = (int)(Odbreader["usuarioC"]);
                        oHRutaDet.Valorizar = (int)(Odbreader["Valorizar"]);
                        oHRutaDet.FechaV = (DateTime)Odbreader["FechaV"];
                        oHRutaDet.usuarioV = (int)(Odbreader["usuarioV"]);
                        oHRutaDet.TFactura = (decimal)Odbreader["TFactura"];
                        oHRutaDet.Cantidad = (decimal)Odbreader["Cantidad"];

                        listaHRutaDet.Add(oHRutaDet);
                    }
                    Odbreader.Close();
                    Odbreader.Dispose();
                }
                if (listaHRutaDet.Count > 0)
                {
                    return listaHRutaDet[0];
                }
                else
                {
                    return hrutadet;
                }
            }
            catch (SqlException sql_ex)
            {
                ExceptionSQL exNew = new ExceptionSQL();
                throw new System.Exception(exNew.exSQL(sql_ex.ToString()).ToString());
            }
            catch (Exception ex)
            {
                ExceptionDAL exNew = new ExceptionDAL();
                throw new System.Exception(exNew.exDAL(ex.ToString()).ToString());
            }
        }
        #endregion

        #region DARptHRuta
        public List<HRutaDet> DARptHRuta(List<HRutaDet> listaHRutaDet)
        {
            try
            {
                return listaHRutaDet;
            }
            catch (Exception ex)
            {
                ExceptionBM exNew = new ExceptionBM();
                throw new System.Exception(exNew.exBM(ex.ToString()).ToString());
            }
        }
        #endregion

    }
}
